import time
import random
import pymysql
import asyncio
import openpyxl

db_2m = {
    'host': 'rm-2zeqn1m3b6ik438mz.mysql.rds.aliyuncs.com',  # 主机
    'user': 'tanfeng',  # 用户名
    'password': 'Tf@654321',  # 密码
    'port': 3306,  # 端口 3306
    'database': 'ppg'  # 数据库名
}

db_wm = {
    'host': 'rm-2zev27ct5v8p9ssnyvo.mysql.rds.aliyuncs.com',  # 主机
    'user': 'tanfeng',  # 用户名
    'password': 'Tf@654321',  # 密码
    'port': 3306,  # 端口 3306
    'database': 'nft'  # 数据库名
}


async def generate_unique_bigint():
    timestamp = int(time.time() * 1000)
    random_number = random.randint(0, 999999)
    unique_bigint = int(str(timestamp) + str(random_number))
    return unique_bigint

async def main():
    # 连接wm库
    connection_wm = pymysql.connect(**db_wm)
    # 创建游标对象
    cursor_wm = connection_wm.cursor()
    # 选择要导入的表名
    xlsx_file_path = "/Users/zxcs/Desktop/tf/2m/server/src/main/resources/藏品现存总量统计.xlsx"
    try:
        cursor_wm.execute("DELETE FROM blade_dict WHERE parent_id=169081328805663566;")
        workbook = openpyxl.load_workbook(xlsx_file_path)
        # Assuming the data is in the first sheet (index 0)
        sheet = workbook.worksheets[0]
        for row in sheet.iter_rows():
            spuName = row[0].value
            spuNumber = row[1].value
            print(f"Column {row[0].value}: {row[1].value}")
            # 插入数据到wm库中的表
            sql_insert = "INSERT INTO blade_dict (id, parent_id, code, dict_key, dict_value, sort) VALUES (%s, %s, %s, %s, %s, %s)"
            unique_id = await generate_unique_bigint()
            data = (unique_id, 169081328805663566, "spuInventory", spuNumber, spuName, 1)
            cursor_wm.execute(sql_insert, data)
        # 提交事务
        connection_wm.commit()
        print("数据表导入成功！")
    except (pymysql.Error) as error:
        print("导入数据表时发生错误:", error)

    # 关闭游标和连接
    cursor_wm.close()
    cursor_wm.close()


if __name__ == '__main__':
    loop = asyncio.get_event_loop()
    loop.run_until_complete(main())